package moddbbackendrevisited.resource;

import java.sql.*;
import javax.ws.rs.*;
import javax.ws.rs.core.*;
import javax.ws.rs.core.Response.*;
import moddbbackendrevisited.Main;
import moddbbackendrevisited.schema.*;

@Path("/tables/{id}/columns")
public class ColumnsResource {

  @Context
  protected UriInfo uriInfo;
  
  /*
   * List All Columns in a Table
   * http://code.google.com/p/moddb08/wiki/BackendInterfaceVersionOne#List_All_Columns_in_a_Table
   */
  @GET
  @Produces("application/xml")
  public Response getAllColumns(@PathParam("id") String id) throws SQLException {
    
    // Get all columns for chosen table
    ResultSet rs = Main.dbCon.getMetaData().getColumns(null, null, id, null);

    // Build Columns schema to respond
    Columns cs = new Columns();

    try {
      if (rs.next()) {
        // Columns found, append to Columns schema
        cs.add(new Column(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), null));
        while (rs.next()) {
            cs.add(new Column(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), null));
        }
        return Response.ok(cs).build();
      } else {
        // 404, no columns found
        return Response.status(Status.NOT_FOUND).build();
      }
    } finally {
      rs.close();
    }
  }
  
  /*
   * Add a Column to a Table
   * http://code.google.com/p/moddb08/wiki/BackendInterfaceVersionOne#Add_a_Column_to_a_Table
   */
  @POST
  @Consumes("application/xml")
  public Response addColumn(@PathParam("id") String tableName, Column c) {
        
    Statement stmnt;

    try {
        stmnt = Main.dbCon.createStatement();
    } catch (SQLException e) {
        return Response.status(Status.SERVICE_UNAVAILABLE).build();
    }

    try {
      // Merge name och mime type to stored column name
      String columnName = c.name + ";" + c.mime;
      // Insert column into chosen table
      stmnt.execute("ALTER TABLE " + tableName + " ADD \"" + columnName + "\" " + c.type);
    } catch (SQLException e) {
      e.printStackTrace();
      return Response.status(Status.CONFLICT).build();
    }

    return Response.created(uriInfo.getAbsolutePath().resolve("/tables/" + tableName + "/columns")).build();//.resolve(table.name)).build();
  }

  /*
   * sqlite does not support deletion of column name, below is a proposal where a copy of the current
   * table is made with the copy droping the chosen column to delete.
   */
  /*
      BEGIN TRANSACTION;
      CREATE TEMPORARY TABLE t1_backup(a,b);
      INSERT INTO t1_backup SELECT a,b FROM t1;
      DROP TABLE t1;
      CREATE TABLE t1(a,b);
      INSERT INTO t1 SELECT a,b FROM t1_backup;
      DROP TABLE t1_backup;
      COMMIT;
  */

  /*
   * Helper method. Returns the full column name (name and mime) given only the name and table,
   * null if not found.
   */
  public static String getFullColumnName(String tableName, String columnName) throws SQLException {

    ResultSet rs = Main.dbCon.getMetaData().getColumns(null, null, tableName, columnName + "%");

    try {
      if (rs.next()) {
        return rs.getString("COLUMN_NAME");
      } else {
        return null;
      }
    } finally {
      rs.close();
    }
  }

}
